from flask import Flask, jsonify, request, make_response
from flask_cors import CORS
from datetime import datetime, timedelta
import os
from dotenv import load_dotenv
import mysql.connector
from mysql.connector import Error

load_dotenv()

app = Flask(__name__)
CORS(app)

# Database configuration
DB_CONFIG = {
    'host': os.getenv('DB_HOST', '47.95.66.56'),
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', 'Gaoge042011'),
    'database': os.getenv('DB_NAME', 'datang_prod_0107')
}

# Module name mapping for each step
STEP_MODULE_MAPPING = {
    'tangzhuang': {
        0: 'AI唐装写真',           # 点击模块
        1: 'AI唐装写真-确认风格',   # 确认风格
        2: '唐装写真'              # 生成结果
    },
    'aivideo': {
        0: 'AI唐风视频',
        1: 'AI唐风视频-确认风格',
        2: '唐风视频'              # 生成结果
    },
    'hanfu': {
        0: '制式汉服',
        1: '制式汉服-确认风格',
        2: '制式汉服'              # 生成结果
    },
    'movie': {
        0: '热门影视剧妆造',
        1: '热门影视剧妆造-确认风格',
        2: '热门电视剧'            # 生成结果
    }
}

def get_db_connection():
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        return connection
    except Error as e:
        print(f"Error connecting to MySQL Database: {e}")
        return None

def get_step_data(cursor, start_date, end_date, module_names):
    if not module_names:
        return []
        
    # 首先获取每个模块的 PV
    pv_query = """
    SELECT 
        module,
        COUNT(*) as pv
    FROM (
        SELECT
            IF(description = '埋点',JSON_UNQUOTE(JSON_EXTRACT(request_args, '$[0].module')),description) as module,
            create_time 
        FROM logs
        WHERE 
            DATE(create_time) BETWEEN %s AND %s
    ) a 
    WHERE module IN ({})
    GROUP BY module
    """.format(','.join(['%s'] * len(module_names)))

    # 然后获取每个模块的 UV（跨天去重）
    uv_query = """
    SELECT 
        module,
        COUNT(DISTINCT open_id) as uv
    FROM (
        SELECT DISTINCT
            IF(description = '埋点',JSON_UNQUOTE(JSON_EXTRACT(request_args, '$[0].module')),description) as module,
            IF(open_id ="",JSON_UNQUOTE(JSON_EXTRACT(request_args, '$[0].openId')),open_id) as open_id
        FROM logs
        WHERE 
            DATE(create_time) BETWEEN %s AND %s
    ) a 
    WHERE module IN ({})
    GROUP BY module
    """.format(','.join(['%s'] * len(module_names)))

    # 执行 PV 查询
    cursor.execute(pv_query, [start_date, end_date] + module_names)
    pv_rows = cursor.fetchall()

    # 执行 UV 查询
    cursor.execute(uv_query, [start_date, end_date] + module_names)
    uv_rows = cursor.fetchall()

    # 合并 PV 和 UV 数据
    result = []
    for pv_row in pv_rows:
        module = pv_row['module']
        uv_row = next((row for row in uv_rows if row['module'] == module), None)
        result.append({
            'module': module,
            'pv': pv_row['pv'],
            'uv': uv_row['uv'] if uv_row else 0
        })
    return result

def get_makeup_generation_data(cursor, start_date, end_date):
    query = """
    SELECT
        style_name,
        COUNT(*) as pv,
        COUNT(DISTINCT open_id) as uv
    FROM (
        SELECT
            CASE 
                WHEN style_id = 1 THEN '唐装写真'
                WHEN style_id = 4 THEN '热门电视剧'
                WHEN style_id = 6 THEN '制式汉服'
                ELSE '未知'
            END as style_name,
            open_id
        FROM flow
        WHERE 
            DATE(create_time) BETWEEN %s AND %s
            AND style_id <> 3 
            AND status = 1
    ) a
    GROUP BY style_name
    """
    cursor.execute(query, [start_date, end_date])
    return cursor.fetchall()

def get_video_generation_data(cursor, start_date, end_date):
    query = """
    SELECT 
        '唐风视频' as model_name,
        COUNT(DISTINCT batch_id) as pv,
        COUNT(DISTINCT open_id) as uv
    FROM (
        SELECT DISTINCT
            batch_id,
            open_id
        FROM flow
        WHERE 
            DATE(create_time) BETWEEN %s AND %s
            AND style_id = 3 
            AND batch_id NOT IN (
                SELECT batch_id 
                FROM flow 
                WHERE style_id = 3 
                AND status = 0 
                GROUP BY batch_id
            )
    ) a
    """
    cursor.execute(query, [start_date, end_date])
    return cursor.fetchall()

def get_overview_data(cursor, start_date, end_date):
    # 获取昨日新增用户数和同比
    yesterday_users_query = """
    WITH yesterday_data AS (
        SELECT COUNT(*) as yesterday_count
        FROM user 
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 1 DAY)
    ),
    before_yesterday_data AS (
        SELECT COUNT(*) as before_yesterday_count
        FROM user 
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 2 DAY)
    ),
    last_week_yesterday_data AS (
        SELECT COUNT(*) as last_week_yesterday_count
        FROM user 
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 7 DAY)
    )
    SELECT 
        yesterday_count,
        before_yesterday_count,
        last_week_yesterday_count,
        ROUND(((yesterday_count - before_yesterday_count) / NULLIF(before_yesterday_count, 0)) * 100, 2) as day_on_day,
        ROUND(((yesterday_count - last_week_yesterday_count) / NULLIF(last_week_yesterday_count, 0)) * 100, 2) as week_on_week
    FROM yesterday_data, before_yesterday_data, last_week_yesterday_data
    """
    
    # 获取今日新增用户数和同比
    new_users_query = """
    WITH today_data AS (
        SELECT COUNT(*) as today_count
        FROM user 
        WHERE DATE(create_time) = %s
    ),
    yesterday_data AS (
        SELECT COUNT(*) as yesterday_count
        FROM user 
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 1 DAY)
    ),
    last_week_data AS (
        SELECT COUNT(*) as last_week_count
        FROM user 
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 7 DAY)
    )
    SELECT 
        today_count,
        yesterday_count,
        last_week_count,
        ROUND(((today_count - yesterday_count) / yesterday_count) * 100, 2) as day_on_day,
        ROUND(((today_count - last_week_count) / last_week_count) * 100, 2) as week_on_week
    FROM today_data, yesterday_data, last_week_data
    """
    
    # 获取今日活跃用户数和同比
    active_users_query = """
    WITH today_data AS (
        SELECT COUNT(DISTINCT open_id) as today_count
        FROM logs
        WHERE DATE(create_time) = %s
    ),
    yesterday_data AS (
        SELECT COUNT(DISTINCT open_id) as yesterday_count
        FROM logs
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 1 DAY)
    ),
    last_week_data AS (
        SELECT COUNT(DISTINCT open_id) as last_week_count
        FROM logs
        WHERE DATE(create_time) = DATE_SUB(%s, INTERVAL 7 DAY)
    )
    SELECT 
        today_count,
        yesterday_count,
        last_week_count,
        ROUND(((today_count - yesterday_count) / yesterday_count) * 100, 2) as day_on_day,
        ROUND(((today_count - last_week_count) / last_week_count) * 100, 2) as week_on_week
    FROM today_data, yesterday_data, last_week_data
    """
    
    # 获取本周新增用户数和同比
    week_users_query = """
    WITH current_week AS (
        SELECT COUNT(*) as current_count
        FROM user 
        WHERE DATE(create_time) BETWEEN DATE_SUB(%s, INTERVAL 6 DAY) AND %s
    ),
    last_week AS (
        SELECT COUNT(*) as last_count
        FROM user 
        WHERE DATE(create_time) BETWEEN DATE_SUB(%s, INTERVAL 13 DAY) AND DATE_SUB(%s, INTERVAL 7 DAY)
    )
    SELECT 
        current_count,
        last_count,
        ROUND(((current_count - last_count) / last_count) * 100, 2) as week_growth
    FROM current_week, last_week
    """
    
    # 获取本月新增用户数和同比
    month_users_query = """
    WITH current_month AS (
        SELECT COUNT(*) as current_count
        FROM user 
        WHERE DATE(create_time) BETWEEN DATE_FORMAT(%s, '%Y-%m-01') AND %s
    ),
    last_month AS (
        SELECT COUNT(*) as last_count
        FROM user 
        WHERE DATE(create_time) BETWEEN DATE_FORMAT(DATE_SUB(%s, INTERVAL 1 MONTH), '%Y-%m-01') 
        AND LAST_DAY(DATE_SUB(%s, INTERVAL 1 MONTH))
    )
    SELECT 
        current_count,
        last_count,
        ROUND(((current_count - last_count) / last_count) * 100, 2) as month_growth
    FROM current_month, last_month
    """
    
    # 获取累计用户数和环比
    total_users_query = """
    WITH current_total AS (
        SELECT COUNT(*) as current_count
        FROM user
        WHERE DATE(create_time) <= %s
    ),
    last_month_total AS (
        SELECT COUNT(*) as last_count
        FROM user
        WHERE DATE(create_time) <= LAST_DAY(DATE_SUB(%s, INTERVAL 1 MONTH))
    )
    SELECT 
        current_count,
        last_count,
        ROUND(((current_count - last_count) / last_count) * 100, 2) as month_growth
    FROM current_total, last_month_total
    """
    
    # 执行查询
    cursor.execute(yesterday_users_query, [end_date, end_date, end_date])
    yesterday_users_data = cursor.fetchone()
    
    cursor.execute(new_users_query, [end_date, end_date, end_date])
    new_users_data = cursor.fetchone()
    
    cursor.execute(active_users_query, [end_date, end_date, end_date])
    active_users_data = cursor.fetchone()
    
    cursor.execute(week_users_query, [end_date, end_date, end_date, end_date])
    week_users_data = cursor.fetchone()
    
    cursor.execute(month_users_query, [end_date, end_date, end_date, end_date])
    month_users_data = cursor.fetchone()
    
    cursor.execute(total_users_query, [end_date, end_date])
    total_users_data = cursor.fetchone()
    
    return {
        'todayNewUsers': {
            'value': new_users_data['today_count'],
            'dayOnDay': new_users_data['day_on_day'],
            'weekOnWeek': new_users_data['week_on_week']
        },
        'todayActiveUsers': {
            'value': active_users_data['today_count'],
            'dayOnDay': active_users_data['day_on_day'],
            'weekOnWeek': active_users_data['week_on_week']
        },
        'weekNewUsers': {
            'value': week_users_data['current_count'],
            'weekOnWeek': week_users_data['week_growth']
        },
        'monthNewUsers': {
            'value': month_users_data['current_count'],
            'monthOnMonth': month_users_data['month_growth']
        },
        'yesterdayNewUsers': {
            'value': yesterday_users_data['yesterday_count'],
            'dayOnDay': yesterday_users_data['day_on_day'],
            'weekOnWeek': yesterday_users_data['week_on_week']
        },
        'totalUsers': {
            'value': total_users_data['current_count'],
            'monthOnMonth': total_users_data['month_growth']
        }
    }

def get_trend_data(cursor, start_date, end_date):
    # 获取每日新增用户数
    new_users_query = """
    SELECT 
        DATE(create_time) as date,
        COUNT(*) as new_users
    FROM user
    WHERE DATE(create_time) BETWEEN %s AND %s
    GROUP BY DATE(create_time)
    ORDER BY date
    """
    
    # 获取每日累计用户数（截至每天的总用户数）
    total_users_query = """
    SELECT 
        dates.date,
        COUNT(DISTINCT u.id) as total_users
    FROM (
        SELECT DATE(create_time) as date
        FROM user
        WHERE DATE(create_time) BETWEEN %s AND %s
        GROUP BY DATE(create_time)
    ) dates
    LEFT JOIN user u ON DATE(u.create_time) <= dates.date
    GROUP BY dates.date
    ORDER BY dates.date
    """
    
    # 获取每日活跃用户数
    active_users_query = """
    SELECT 
        DATE(create_time) as date,
        COUNT(DISTINCT open_id) as active_users
    FROM logs
    WHERE DATE(create_time) BETWEEN %s AND %s
    GROUP BY DATE(create_time)
    ORDER BY date
    """
    
    # 执行查询
    cursor.execute(new_users_query, [start_date, end_date])
    new_users_data = cursor.fetchall()
    
    cursor.execute(total_users_query, [start_date, end_date])
    total_users_data = cursor.fetchall()
    
    cursor.execute(active_users_query, [start_date, end_date])
    active_users_data = cursor.fetchall()
    
    # 合并数据
    result = {}
    
    # 初始化日期范围内的所有日期
    current_date = datetime.strptime(start_date, '%Y-%m-%d')
    end = datetime.strptime(end_date, '%Y-%m-%d')
    while current_date <= end:
        date_str = current_date.strftime('%Y-%m-%d')
        result[date_str] = {
            'date': date_str,
            'newUsers': 0,
            'totalUsers': 0,
            'activeUsers': 0
        }
        current_date += timedelta(days=1)
    
    # 填充数据
    for row in new_users_data:
        date_str = row['date'].strftime('%Y-%m-%d')
        result[date_str]['newUsers'] = row['new_users']
    
    for row in total_users_data:
        date_str = row['date'].strftime('%Y-%m-%d')
        result[date_str]['totalUsers'] = row['total_users']
    
    for row in active_users_data:
        date_str = row['date'].strftime('%Y-%m-%d')
        result[date_str]['activeUsers'] = row['active_users']
    
    # 转换为列表并排序
    trend_data = list(result.values())
    trend_data.sort(key=lambda x: x['date'])
    
    return trend_data

@app.route('/api/user-behavior/funnel', methods=['GET'])
def get_user_behavior_funnel():
    try:
        # Get query parameters
        metrics = request.args.getlist('metrics[]')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')

        if not start_date or not end_date:
            return jsonify({'error': '必须提供开始和结束日期'}), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)
        
        # Get data for steps 0 and 1 (from logs table)
        all_modules = []
        for metric in metrics:
            if metric in STEP_MODULE_MAPPING:
                for step in [0, 1]:  # Only get first two steps from logs
                    if step in STEP_MODULE_MAPPING[metric]:
                        all_modules.append(STEP_MODULE_MAPPING[metric][step])

        # Get data for all steps
        step_rows = get_step_data(cursor, start_date, end_date, all_modules)
        
        # Get generation results data
        makeup_rows = get_makeup_generation_data(cursor, start_date, end_date)
        video_rows = get_video_generation_data(cursor, start_date, end_date)

        # Process results
        result = {}
        for metric in metrics:
            if metric in STEP_MODULE_MAPPING:
                pv_data = []
                uv_data = []
                
                # Process steps 0 and 1
                for step in range(2):
                    if step in STEP_MODULE_MAPPING[metric]:
                        module_name = STEP_MODULE_MAPPING[metric][step]
                        module_data = next((row for row in step_rows if row['module'] == module_name), None)
                        
                        if module_data:
                            pv_data.append(module_data['pv'])
                            uv_data.append(module_data['uv'])
                        else:
                            pv_data.append(0)
                            uv_data.append(0)
                
                # Process step 2 (generation results)
                style_name = STEP_MODULE_MAPPING[metric][2]
                if metric == 'aivideo':
                    # Handle video generation data
                    video_data = next((row for row in video_rows if row['model_name'] == '唐风视频'), None)
                    if video_data:
                        pv_data.append(video_data['pv'])
                        uv_data.append(video_data['uv'])
                    else:
                        pv_data.append(0)
                        uv_data.append(0)
                else:
                    # Handle makeup generation data
                    makeup_data = next((row for row in makeup_rows if row['style_name'] == style_name), None)
                    if makeup_data:
                        pv_data.append(makeup_data['pv'])
                        uv_data.append(makeup_data['uv'])
                    else:
                        pv_data.append(0)
                        uv_data.append(0)
                
                result[metric] = {
                    'pv': pv_data,
                    'uv': uv_data
                }

        cursor.close()
        conn.close()

        return jsonify(result)

    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/overview', methods=['GET'])
def get_overview():
    try:
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')

        if not start_date or not end_date:
            return jsonify({'error': '必须提供开始和结束日期'}), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)
        overview_data = get_overview_data(cursor, start_date, end_date)
        cursor.close()
        conn.close()

        return jsonify(overview_data)

    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/user-trend', methods=['GET'])
def get_user_trend():
    try:
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')

        if not start_date or not end_date:
            return jsonify({'error': '必须提供开始和结束日期'}), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({'error': 'Database connection failed'}), 500

        cursor = conn.cursor(dictionary=True)
        trend_data = get_trend_data(cursor, start_date, end_date)
        cursor.close()
        conn.close()

        return jsonify(trend_data)

    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'error': str(e)}), 500

# 获取妆造分析总览数据
@app.route('/api/makeup-analysis/overview', methods=['GET'])
def get_makeup_overview():
    try:
        print("Received request for makeup overview")  # Debug log
        # 获取时间范围参数
        time_range = request.args.get('timeRange', 'today')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')
        style_ids = request.args.getlist('styleIds[]')
        
        print(f"Parameters: time_range={time_range}, start_date={start_date}, end_date={end_date}, style_ids={style_ids}")  # Debug log

        # 根据timeRange计算日期范围
        today = datetime.now().date()
        if not start_date or not end_date:
            if time_range == 'today':
                start_date = end_date = today.strftime('%Y-%m-%d')
            elif time_range == 'week':
                start_date = (today - timedelta(days=6)).strftime('%Y-%m-%d')
                end_date = today.strftime('%Y-%m-%d')
            elif time_range == 'month':
                start_date = today.replace(day=1).strftime('%Y-%m-%d')
                end_date = today.strftime('%Y-%m-%d')
                
        print(f"Calculated date range: {start_date} to {end_date}")  # Debug log

        conn = get_db_connection()
        if not conn:
            print("Database connection failed")  # Debug log
            return jsonify({'success': False, 'message': '数据库连接失败'}), 500

        cursor = conn.cursor(dictionary=True)

        # 构建style_id条件
        style_condition = "AND style_id <> 3"
        params = [start_date, end_date]
        if style_ids:
            style_condition = "AND style_id IN ({})".format(','.join(['%s'] * len(style_ids)))
            params = [start_date, end_date] + style_ids
            
        print(f"SQL condition: {style_condition}")  # Debug log
        print(f"SQL params: {params}")  # Debug log

        # 获取当前数据
        current_query = """
        SELECT
            COALESCE(COUNT(open_id), 0) as total_generation_count,
            COALESCE(COUNT(DISTINCT open_id), 0) as total_unique_users,
            COALESCE(ROUND(AVG(generation_time), 2), 0) as avg_process_time,
            COALESCE(SUM(share_num), 0) as total_shares,
            COALESCE(COUNT(DISTINCT CASE WHEN status = 0 THEN batch_id END), 0) as total_fail_count
        FROM flow
        WHERE 
            DATE(create_time) BETWEEN %s AND %s
            {}
        """.format(style_condition)

        # 获取昨日数据
        yesterday = today - timedelta(days=1)
        yesterday_query = """
        SELECT
            COALESCE(COUNT(open_id), 0) as total_generation_count,
            COALESCE(ROUND(AVG(generation_time), 2), 0) as avg_process_time,
            COALESCE(SUM(share_num), 0) as total_shares,
            COALESCE(COUNT(DISTINCT CASE WHEN status = 0 THEN batch_id END), 0) as total_fail_count
        FROM flow
        WHERE 
            DATE(create_time) = %s
            {}
        """.format(style_condition)

        # 获取上周同期数据
        last_week = today - timedelta(days=7)
        last_week_query = """
        SELECT
            COALESCE(COUNT(open_id), 0) as total_generation_count,
            COALESCE(ROUND(AVG(generation_time), 2), 0) as avg_process_time,
            COALESCE(SUM(share_num), 0) as total_shares,
            COALESCE(COUNT(DISTINCT CASE WHEN status = 0 THEN batch_id END), 0) as total_fail_count
        FROM flow
        WHERE 
            DATE(create_time) = %s
            {}
        """.format(style_condition)

        # 执行查询
        cursor.execute(current_query, params)
        current_data = cursor.fetchone()
        print(f"Current data: {current_data}")  # Debug log
        
        # 构建昨日和上周查询参数
        yesterday_params = [yesterday.strftime('%Y-%m-%d')]
        last_week_params = [last_week.strftime('%Y-%m-%d')]
        if style_ids:
            yesterday_params.extend(style_ids)
            last_week_params.extend(style_ids)
            
        cursor.execute(yesterday_query, yesterday_params)
        yesterday_data = cursor.fetchone()
        print(f"Yesterday data: {yesterday_data}")  # Debug log
        
        cursor.execute(last_week_query, last_week_params)
        last_week_data = cursor.fetchone()
        print(f"Last week data: {last_week_data}")  # Debug log
        
        cursor.close()
        conn.close()

        # 构建返回数据结构
        result = {
            'success': True,
            'data': {
                'totalGenerationCount': {
                    'value': current_data['total_generation_count'],
                    'dayOnDay': calculate_growth_rate(current_data['total_generation_count'], yesterday_data['total_generation_count']),
                    'weekOnWeek': calculate_growth_rate(current_data['total_generation_count'], last_week_data['total_generation_count'])
                },
                'totalShares': {
                    'value': current_data['total_shares'],
                    'dayOnDay': calculate_growth_rate(current_data['total_shares'], yesterday_data['total_shares']),
                    'weekOnWeek': calculate_growth_rate(current_data['total_shares'], last_week_data['total_shares'])
                },
                'avgProcessTime': {
                    'value': current_data['avg_process_time'],
                    'dayOnDay': calculate_growth_rate(current_data['avg_process_time'], yesterday_data['avg_process_time']),
                    'weekOnWeek': calculate_growth_rate(current_data['avg_process_time'], last_week_data['avg_process_time'])
                },
                'totalFailCount': {
                    'value': current_data['total_fail_count'],
                    'dayOnDay': calculate_growth_rate(current_data['total_fail_count'], yesterday_data['total_fail_count']),
                    'weekOnWeek': calculate_growth_rate(current_data['total_fail_count'], last_week_data['total_fail_count'])
                }
            }
        }
        
        print(f"Returning result: {result}")  # Debug log
        return jsonify(result)

    except Exception as e:
        print(f"Error in get_makeup_overview: {str(e)}")  # Debug log
        return jsonify({'success': False, 'message': str(e)}), 500

# 获取妆造分析性别维度数据
@app.route('/api/makeup-analysis/gender', methods=['GET'])
def get_makeup_gender_stats():
    try:
        print("Received request for makeup gender stats")  # Debug log
        # 获取时间范围参数
        time_range = request.args.get('timeRange', 'today')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')
        style_ids = request.args.getlist('styleIds[]')
        
        print(f"Parameters: time_range={time_range}, start_date={start_date}, end_date={end_date}, style_ids={style_ids}")  # Debug log
        
        # 根据timeRange计算日期范围
        today = datetime.now().date()
        if not start_date or not end_date:
            if time_range == 'today':
                start_date = end_date = today.strftime('%Y-%m-%d')
            elif time_range == 'week':
                start_date = (today - timedelta(days=6)).strftime('%Y-%m-%d')
                end_date = today.strftime('%Y-%m-%d')
            elif time_range == 'month':
                start_date = today.replace(day=1).strftime('%Y-%m-%d')
                end_date = today.strftime('%Y-%m-%d')
                
        print(f"Calculated date range: {start_date} to {end_date}")  # Debug log
        
        conn = get_db_connection()
        if not conn:
            print("Database connection failed")  # Debug log
            return jsonify({'success': False, 'message': '数据库连接失败'}), 500

        cursor = conn.cursor(dictionary=True)
        
        # 构建style_id条件
        style_condition = "AND style_id <> 3"
        params = [start_date, end_date]
        if style_ids:
            style_condition = "AND style_id IN ({})".format(','.join(['%s'] * len(style_ids)))
            params = [start_date, end_date] + style_ids
            
        print(f"SQL condition: {style_condition}")  # Debug log
        print(f"SQL params: {params}")  # Debug log

        # 获取性别维度数据
        gender_query = f"""
        WITH gender_stats AS (
            SELECT
                CASE 
                    WHEN style_id = 1 THEN '唐装写真'
                    WHEN style_id = 4 THEN '热门电视剧'
                    WHEN style_id = 6 THEN '制式汉服'
                    ELSE '未知'
                END as style_name,
                CASE 
                    WHEN sex = 1 THEN '男'
                    WHEN sex = 2 THEN '女'
                    ELSE '未知'
                END as gender,
                COUNT(open_id) as generation_count,
                COUNT(DISTINCT open_id) as unique_users,
                COALESCE(SUM(share_num), 0) as total_shares
            FROM flow
            WHERE 
                DATE(create_time) BETWEEN %s AND %s
                {style_condition}
                AND status = 1
                AND sex IN (1, 2)  -- 只统计明确的性别
            GROUP BY 
                style_name,
                gender
        )
        SELECT 
            style_name,
            gender,
            generation_count,
            unique_users,
            total_shares,
            ROUND(
                generation_count * 100.0 / NULLIF(SUM(generation_count) OVER (PARTITION BY style_name), 0),
                2
            ) as generation_percentage,
            ROUND(
                unique_users * 100.0 / NULLIF(SUM(unique_users) OVER (PARTITION BY style_name), 0),
                2
            ) as users_percentage,
            ROUND(
                total_shares * 100.0 / NULLIF(SUM(total_shares) OVER (PARTITION BY style_name), 0),
                2
            ) as shares_percentage
        FROM gender_stats
        ORDER BY 
            style_name,
            gender
        """

        cursor.execute(gender_query, params)
        gender_stats = cursor.fetchall()
        print(f"Gender stats: {gender_stats}")  # Debug log

        # 重组数据结构以适应前端图表需求
        result = {}
        for stat in gender_stats:
            style = stat['style_name']
            if style not in result:
                result[style] = {
                    'generation': [],
                    'users': [],
                    'shares': []
                }
            
            # 生图次数
            result[style]['generation'].append({
                'name': stat['gender'],
                'value': stat['generation_percentage'] or 0,
                'count': stat['generation_count'] or 0  # 添加实际数量
            })
            
            # 生图人数
            result[style]['users'].append({
                'name': stat['gender'],
                'value': stat['users_percentage'] or 0,
                'count': stat['unique_users'] or 0  # 添加实际数量
            })
            
            # 分享次数
            result[style]['shares'].append({
                'name': stat['gender'],
                'value': stat['shares_percentage'] or 0,
                'count': stat['total_shares'] or 0  # 添加实际数量
            })

        print(f"Returning result: {result}")  # Debug log
        return jsonify({
            'success': True,
            'data': result
        })

    except Exception as e:
        print(f"Error in get_makeup_gender_stats: {str(e)}")  # Debug log
        return jsonify({'success': False, 'message': str(e)}), 500

def calculate_growth_rate(current, previous):
    # 如果任一值为 None，返回 0
    if current is None or previous is None:
        return 0
    # 将值转换为 float 类型
    current = float(current)
    previous = float(previous)
    # 如果前值为 0，且当前值不为 0，返回 100%（表示从无到有）
    if previous == 0 and current != 0:
        return 100
    # 如果前值为 0，且当前值也为 0，返回 0（表示无变化）
    if previous == 0:
        return 0
    # 正常计算增长率
    return round(((current - previous) / previous) * 100, 2)

@app.route('/api/video-analysis/overview')
def get_video_overview():
    try:
        # 获取查询参数
        time_range = request.args.get('timeRange')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')
        
        # 设置日期范围
        if time_range == 'today':
            start_date = end_date = datetime.now().strftime('%Y-%m-%d')
        elif time_range == 'week':
            end_date = datetime.now().strftime('%Y-%m-%d')
            start_date = (datetime.now() - timedelta(days=6)).strftime('%Y-%m-%d')
        elif time_range == 'month':
            today = datetime.now()
            start_date = today.replace(day=1).strftime('%Y-%m-%d')
            end_date = today.strftime('%Y-%m-%d')
        elif not all([start_date, end_date]):
            return jsonify({
                'success': False,
                'message': '无效的日期范围'
            }), 400

        # 获取数据库连接
        conn = get_db_connection()
        if not conn:
            return jsonify({
                'success': False,
                'message': '数据库连接失败'
            }), 500

        try:
            cursor = conn.cursor(dictionary=True)
            
            # 计算比较日期范围
            yesterday_start = (datetime.strptime(start_date, '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
            yesterday_end = (datetime.strptime(end_date, '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
            
            last_week_start = (datetime.strptime(start_date, '%Y-%m-%d') - timedelta(days=7)).strftime('%Y-%m-%d')
            last_week_end = (datetime.strptime(end_date, '%Y-%m-%d') - timedelta(days=7)).strftime('%Y-%m-%d')
            
            # 获取当前时间段的数据
            current_query = """
            WITH daily_stats AS (
                SELECT 
                    DATE(create_time) as date,
                    COUNT(DISTINCT batch_id) as image_count,
                    COUNT(DISTINCT open_id) as user_count,
                    COUNT(DISTINCT CASE 
                        WHEN remark IS NOT NULL THEN batch_id 
                        ELSE NULL 
                    END) as fail_count
                FROM flow
                WHERE 
                    DATE(create_time) BETWEEN %s AND %s
                    AND style_id = 3
                GROUP BY DATE(create_time)
                ORDER BY date DESC
            ),
            video_stats AS (
                SELECT 
                    create_time,
                    COUNT(DISTINCT batch_id) as batch_count,
                    SUM(max_time) as total_time,
                    SUM(max_time)/COUNT(DISTINCT batch_id) as avg_time
                FROM (
                    SELECT 
                        create_time,
                        batch_id,
                        max_time
                    FROM (
                        SELECT
                            DATE(create_time) as create_time,
                            batch_id,
                            MAX(generation_time) OVER (PARTITION BY batch_id) AS max_time
                        FROM flow
                        WHERE 
                            DATE(create_time) BETWEEN %s AND %s
                            AND style_id = 3 
                            AND batch_id NOT IN (
                                SELECT batch_id 
                                FROM flow 
                                WHERE style_id = 3 
                                AND status = 0 
                                GROUP BY batch_id
                            )
                    ) a 
                    GROUP BY create_time, batch_id, max_time
                ) a 
                GROUP BY create_time
            )
            SELECT 
                COALESCE(SUM(d.image_count), 0) as video_count,
                COALESCE(COUNT(DISTINCT d.user_count), 0) as user_count,
                COALESCE(SUM(d.fail_count), 0) as fail_count,
                COALESCE(SUM(v.total_time) / NULLIF(SUM(v.batch_count), 0), 0) as avg_process_time
            FROM daily_stats d
            LEFT JOIN video_stats v ON d.date = v.create_time
            """
            
            # 执行查询
            cursor.execute(current_query, (start_date, end_date, start_date, end_date))
            current_data = cursor.fetchone()

            cursor.execute(current_query, (yesterday_start, yesterday_end, yesterday_start, yesterday_end))
            yesterday_data = cursor.fetchone()

            cursor.execute(current_query, (last_week_start, last_week_end, last_week_start, last_week_end))
            last_week_data = cursor.fetchone()

            # 计算环比和同比
            def calculate_change(current, previous):
                # 如果任一值为 None，返回 0
                if current is None or previous is None:
                    return 0
                # 将值转换为 float 类型
                current = float(current)
                previous = float(previous)
                # 如果前值为 0，且当前值不为 0，返回 100%（表示从无到有）
                if previous == 0 and current != 0:
                    return 100
                # 如果前值为 0，且当前值也为 0，返回 0（表示无变化）
                if previous == 0:
                    return 0
                # 正常计算增长率
                return round(((current - previous) / previous) * 100, 2)

            # 构造返回数据
            result = {
                'totalVideoCount': {
                    'value': current_data['video_count'],
                    'dayOnDay': calculate_change(
                        current_data['video_count'],
                        yesterday_data['video_count']
                    ),
                    'weekOnWeek': calculate_change(
                        current_data['video_count'],
                        last_week_data['video_count']
                    )
                },
                'totalUserCount': {
                    'value': current_data['user_count'],
                    'dayOnDay': calculate_change(
                        current_data['user_count'],
                        yesterday_data['user_count']
                    ),
                    'weekOnWeek': calculate_change(
                        current_data['user_count'],
                        last_week_data['user_count']
                    )
                },
                'avgProcessTime': {
                    'value': float(current_data['avg_process_time']) if current_data['avg_process_time'] is not None else 0,
                    'dayOnDay': calculate_change(
                        current_data['avg_process_time'],
                        yesterday_data['avg_process_time']
                    ),
                    'weekOnWeek': calculate_change(
                        current_data['avg_process_time'],
                        last_week_data['avg_process_time']
                    )
                },
                'totalFailCount': {
                    'value': current_data['fail_count'],
                    'dayOnDay': calculate_change(
                        current_data['fail_count'],
                        yesterday_data['fail_count']
                    ),
                    'weekOnWeek': calculate_change(
                        current_data['fail_count'],
                        last_week_data['fail_count']
                    )
                }
            }

            return jsonify({
                'success': True,
                'data': result
            })

        except Exception as e:
            print(f"Database error in get_video_overview: {str(e)}")
            return jsonify({
                'success': False,
                'message': f'数据库查询错误: {str(e)}'
            }), 500
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    except Exception as e:
        print(f"Error in get_video_overview: {str(e)}")
        return jsonify({
            'success': False,
            'message': f'服务器错误: {str(e)}'
        }), 500

@app.route('/api/video-analysis/details')
def get_video_details():
    try:
        # 获取查询参数
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')
        
        if not all([start_date, end_date]):
            return jsonify({'success': False, 'message': '请提供开始和结束日期'})

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # 获取图片上传数量分布
        image_count_query = """
        SELECT 
            COUNT(*) as count,
            COUNT(DISTINCT batch_id) as batch_count
        FROM flow 
        WHERE style_id = 3 
        AND DATE(create_time) BETWEEN %s AND %s
        GROUP BY batch_id
        """

        cursor.execute(image_count_query, (start_date, end_date))
        image_counts = cursor.fetchall()

        # 处理数据，计算分布
        distribution = {}
        total_videos = 0
        for row in image_counts:
            count = row['count']
            total_videos += 1
            distribution[str(count)] = distribution.get(str(count), 0) + 1

        # 转换为前端需要的格式
        result = [
            {'name': f'{count}张', 'value': videos}
            for count, videos in distribution.items()
        ]

        cursor.close()
        conn.close()

        return jsonify({
            'success': True,
            'data': result
        })

    except Exception as e:
        print(f"Error in get_video_details: {str(e)}")
        return jsonify({
            'success': False,
            'message': '获取视频详细数据失败'
        })

@app.route('/api/video-analysis/failures')
def get_video_failures():
    try:
        # 获取查询参数
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')
        
        if not start_date or not end_date:
            return jsonify({
                'success': False,
                'message': '请提供开始和结束日期'
            }), 400

        # 连接数据库
        conn = get_db_connection()
        if not conn:
            return jsonify({
                'success': False,
                'message': '数据库连接失败'
            }), 500

        try:
            cursor = conn.cursor(dictionary=True)
            
            # 查询失败原因统计
            sql = """
            WITH failure_stats AS (
                SELECT 
                    remark as failure_reason,
                    COUNT(DISTINCT batch_id) as count
                FROM flow
                WHERE 
                    DATE(create_time) BETWEEN %s AND %s
                    AND style_id = 3
                    AND remark IS NOT NULL
                GROUP BY failure_reason
            )
            SELECT 
                COALESCE(failure_reason, '未知原因') as name,
                count as value
            FROM failure_stats
            ORDER BY count DESC
            """
            
            cursor.execute(sql, (start_date, end_date))
            results = cursor.fetchall()
            
            return jsonify({
                'success': True,
                'data': results
            })
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        print('Error in get_video_failures:', str(e))
        return jsonify({
            'success': False,
            'message': '获取失败原因统计失败'
        }), 500

# 用户管理相关接口
@app.route('/api/users', methods=['GET'])
def get_users():
    try:
        # 获取查询参数
        phone = request.args.get('phone')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')

        # 构建基础查询
        query = """
        SELECT 
            id,              -- 用户ID
            user_name,       -- 用户名称
            phone,          -- 用户手机号
            score,          -- 用户积分
            is_pay,         -- 是否支付
            is_member,      -- 是否会员
            create_time     -- 注册时间
        FROM `user`
        WHERE 1=1
        """
        params = []

        # 添加筛选条件
        if phone:
            query += " AND phone LIKE %s"
            params.append(f"%{phone}%")
        
        # 根据注册时间筛选
        if start_date:
            query += " AND DATE(create_time) >= %s"
            params.append(start_date)
        
        if end_date:
            query += " AND DATE(create_time) <= %s"
            params.append(end_date)

        # 添加排序
        query += " ORDER BY create_time DESC"

        # 执行查询
        conn = get_db_connection()
        if not conn:
            return jsonify({
                'success': False,
                'message': '数据库连接失败',
                'data': []
            }), 500

        cursor = conn.cursor(dictionary=True)
        cursor.execute(query, params)
        users = cursor.fetchall()

        # 格式化数据
        formatted_users = []
        for user in users:
            formatted_users.append({
                'id': str(user['id']),
                'name': user['user_name'] or '',
                'phone': user['phone'] or '',
                'points': user['score'] or 0,
                'isMember': bool(user['is_member']),
                'hasPaid': bool(user['is_pay']),
                'registerTime': user['create_time'].isoformat() if user['create_time'] else None
            })

        cursor.close()
        conn.close()

        return jsonify({
            'success': True,
            'message': '获取用户列表成功',
            'data': formatted_users
        })

    except Exception as e:
        print(f"Error in get_users: {str(e)}")
        return jsonify({
            'success': False,
            'message': str(e),
            'data': []
        }), 500

@app.route('/api/users/<user_id>/membership', methods=['PUT'])
def update_user_membership(user_id):
    try:
        data = request.json
        is_member = data.get('isMember')

        if is_member is None:
            return jsonify({'success': False, 'message': '缺少必要参数'}), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({'success': False, 'message': '数据库连接失败'}), 500

        cursor = conn.cursor()
        query = "UPDATE `user` SET is_member = %s WHERE id = %s"
        cursor.execute(query, (is_member, user_id))
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({'success': True})

    except Exception as e:
        print(f"Error in update_user_membership: {str(e)}")
        return jsonify({'success': False, 'message': str(e)}), 500

@app.route('/api/users/<user_id>/points', methods=['PUT'])
def update_user_points(user_id):
    try:
        data = request.json
        points = data.get('points')

        if points is None:
            return jsonify({'success': False, 'message': '缺少必要参数'}), 400

        conn = get_db_connection()
        if not conn:
            return jsonify({'success': False, 'message': '数据库连接失败'}), 500

        cursor = conn.cursor()
        query = "UPDATE `user` SET score = %s WHERE id = %s"
        cursor.execute(query, (points, user_id))
        conn.commit()

        cursor.close()
        conn.close()

        return jsonify({'success': True})

    except Exception as e:
        print(f"Error in update_user_points: {str(e)}")
        return jsonify({'success': False, 'message': str(e)}), 500

# 添加健康检查端点
@app.route('/api/health')
def health_check():
    return jsonify({"status": "healthy"}), 200

# 导出妆造分析数据
@app.route('/api/makeup-analysis/export', methods=['GET'])
def export_makeup_data():
    try:
        # 获取查询参数
        time_range = request.args.get('timeRange', 'today')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')

        # 设置日期范围
        today = datetime.now().date()
        if not start_date or not end_date:
            if time_range == 'today':
                start_date = end_date = today.strftime('%Y-%m-%d')
            elif time_range == 'week':
                start_date = (today - timedelta(days=6)).strftime('%Y-%m-%d')
                end_date = today.strftime('%Y-%m-%d')
            elif time_range == 'month':
                start_date = today.replace(day=1).strftime('%Y-%m-%d')
                end_date = today.strftime('%Y-%m-%d')

        conn = get_db_connection()
        if not conn:
            return jsonify({'success': False, 'message': '数据库连接失败'}), 500

        cursor = conn.cursor(dictionary=True)

        # 构建查询条件
        style_condition = "AND style_id <> 3"
        params = [start_date, end_date]
        if style_ids:
            style_condition = "AND style_id IN ({})".format(','.join(['%s'] * len(style_ids)))
            params = [start_date, end_date] + style_ids

        # 查询详细数据
        query = f"""
        SELECT 
            DATE(f.create_time) as date,
            CASE 
                WHEN f.style_id = 1 THEN '唐装写真'
                WHEN f.style_id = 4 THEN '热门电视剧'
                WHEN f.style_id = 6 THEN '制式汉服'
                ELSE '未知'
            END as style_name,
            CASE 
                WHEN f.sex = 1 THEN '男'
                WHEN f.sex = 2 THEN '女'
                ELSE '未知'
            END as gender,
            COUNT(f.open_id) as generation_count,
            COUNT(DISTINCT f.open_id) as unique_users,
            SUM(f.share_num) as share_count,
            ROUND(AVG(f.generation_time), 2) as avg_process_time,
            COUNT(CASE WHEN f.status = 0 THEN 1 END) as fail_count
        FROM flow f
        WHERE 
            DATE(f.create_time) BETWEEN %s AND %s
            {style_condition}
        GROUP BY 
            DATE(f.create_time),
            style_name,
            gender
        ORDER BY 
            date DESC,
            style_name,
            gender
        """

        cursor.execute(query, params)
        data = cursor.fetchall()
        cursor.close()
        conn.close()

        # 转换为CSV格式
        output = []
        # 添加表头
        output.append([
            '日期',
            '模块',
            '性别',
            '生图次数',
            '生图人数',
            '分享次数',
            '平均处理时长(秒)',
            '失败次数'
        ])
        
        # 添加数据行
        for row in data:
            output.append([
                row['date'].strftime('%Y-%m-%d'),
                row['style_name'],
                row['gender'],
                row['generation_count'],
                row['unique_users'],
                row['share_count'],
                row['avg_process_time'],
                row['fail_count']
            ])

        # 设置响应头，使浏览器下载文件
        filename = f'makeup_analysis_{start_date}_to_{end_date}.csv'
        response = make_response('\n'.join([','.join(map(str, row)) for row in output]))
        response.headers['Content-Type'] = 'text/csv'
        response.headers['Content-Disposition'] = f'attachment; filename={filename}'
        
        return response

    except Exception as e:
        print(f"Error in export_makeup_data: {str(e)}")
        return jsonify({'success': False, 'message': str(e)}), 500

@app.route('/api/video-analysis/export')
def export_video_analysis():
    try:
        # 获取查询参数
        time_range = request.args.get('timeRange')
        start_date = request.args.get('startDate')
        end_date = request.args.get('endDate')
        
        if not time_range:
            return jsonify({
                'success': False,
                'message': '请提供时间范围'
            }), 400

        # 根据timeRange计算日期范围
        today = datetime.now().date()
        if time_range == 'today':
            start_date = end_date = today.strftime('%Y-%m-%d')
        elif time_range == 'week':
            end_date = today.strftime('%Y-%m-%d')
            start_date = (today - timedelta(days=6)).strftime('%Y-%m-%d')
        elif time_range == 'month':
            start_date = today.replace(day=1).strftime('%Y-%m-%d')
            end_date = today.strftime('%Y-%m-%d')
        elif time_range == 'custom':
            if not start_date or not end_date:
                return jsonify({
                    'success': False,
                    'message': '自定义时间范围需要提供开始和结束日期'
                }), 400

        # 连接数据库
        conn = get_db_connection()
        if not conn:
            return jsonify({
                'success': False,
                'message': '数据库连接失败'
            }), 500

        try:
            cursor = conn.cursor(dictionary=True)
            
            # 查询每日数据
            sql = """
            WITH video_stats AS (
                SELECT 
                    create_time,
                    COUNT(DISTINCT batch_id) as batch_count,
                    SUM(max_time) as total_time,
                    SUM(max_time)/COUNT(DISTINCT batch_id) as avg_time
                FROM (
                    SELECT 
                        create_time,
                        batch_id,
                        max_time
                    FROM (
                        SELECT
                            DATE(create_time) as create_time,
                            batch_id,
                            MAX(generation_time) OVER (PARTITION BY batch_id) AS max_time
                        FROM flow
                        WHERE 
                            DATE(create_time) BETWEEN %s AND %s
                            AND style_id = 3 
                            AND batch_id NOT IN (
                                SELECT batch_id 
                                FROM flow 
                                WHERE style_id = 3 
                                AND status = 0 
                                GROUP BY batch_id
                            )
                    ) a 
                    GROUP BY create_time, batch_id, max_time
                ) a 
                GROUP BY create_time
            )
            SELECT 
                v.create_time as date,
                v.batch_count as video_count,
                COUNT(DISTINCT f.open_id) as user_count,
                COALESCE(v.avg_time, 0) as avg_process_time,
                COUNT(DISTINCT CASE WHEN f.remark IS NOT NULL THEN f.batch_id ELSE NULL END) as fail_count
            FROM video_stats v
            LEFT JOIN flow f ON DATE(f.create_time) = v.create_time
                AND f.style_id = 3
                AND f.batch_id NOT IN (
                    SELECT batch_id 
                    FROM flow 
                    WHERE style_id = 3 
                    AND status = 0 
                    GROUP BY batch_id
                )
            GROUP BY 
                v.create_time,
                v.batch_count,
                v.avg_time
            ORDER BY date DESC
            """
            
            cursor.execute(sql, (start_date, end_date))
            results = cursor.fetchall()
            
            # 格式化数据
            data = [{
                'date': row['date'].strftime('%Y-%m-%d'),
                'imageCount': row['video_count'],
                'userCount': row['user_count'],
                'avgProcessTime': float(row['avg_process_time']) if row['avg_process_time'] else 0,
                'failCount': row['fail_count']
            } for row in results]
            
            return jsonify({
                'success': True,
                'data': data
            })
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        print('Error in export_video_analysis:', str(e))
        return jsonify({
            'success': False,
            'message': '导出数据失败'
        }), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000) 